package com.kcsj.dao;
//李延杰
import com.kcsj.domain.Warehouse;
import com.kcsj.util.Condition;
import com.kcsj.util.JdbcHelper;
import com.kcsj.util.Pagination;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.List;

public class WarehouseDao {
    private static final WarehouseDao wareHouseDao = new WarehouseDao();

    public static WarehouseDao getInstance(){
        return wareHouseDao;
    }

    //查找school表中所有数据
    public Collection<Warehouse> findAll(Connection conn) throws SQLException {
        ArrayList<Warehouse> deliveryPeoples = new ArrayList<>();
        //预编译方式进行查询
        StringBuilder sql = new StringBuilder("select * from warehouse");
        PreparedStatement preparedStatement = conn.prepareStatement(sql.toString());
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()){
            String warehouseNo = resultSet.getString(1);
            String name = resultSet.getString(2);
            String address = resultSet.getString(3);
            Double volume = resultSet.getDouble(4);
            Double maxVolume = resultSet.getDouble(5);
            Warehouse warehouse = new Warehouse(warehouseNo,name,address,volume,maxVolume);
            deliveryPeoples.add(warehouse);
        }
        JdbcHelper.close(resultSet,preparedStatement);
        return deliveryPeoples;
    }


    //根据id值查找Administrator表中相应数据
    public Warehouse find(String warehouseNo, Connection conn) throws SQLException {
        Warehouse warehouse = null;
        //预编译方式进行查询
        String s = "select * from warehouse where  warehouseNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,warehouseNo);
        ResultSet resultSet = preparedStatement.executeQuery();
        //将resultSet转换为json字符串
        if (resultSet.next()){
            String name = resultSet.getString(2);
            String address = resultSet.getString(3);
            Double volume = resultSet.getDouble(4);
            Double maxVolume = resultSet.getDouble(5);
            warehouse = new Warehouse(warehouseNo,name,address,volume,maxVolume);
        }
        //关闭连接
        JdbcHelper.close(resultSet,preparedStatement);
        return warehouse;
    }

    //修改school表中的数据
    public boolean update(Warehouse warehouse, Connection conn) throws SQLException {
        //预编译方式进行修改数据
        String s = "update warehouse set name=?,address=?,volume=?,MaxVolume=? where warehouseNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,warehouse.getName());
        preparedStatement.setString(2,warehouse.getAddress());
        preparedStatement.setDouble(3,warehouse.getVolume());
        preparedStatement.setDouble(4,warehouse.getMaxVolume());
        preparedStatement.setString(5,warehouse.getWarehouseNo());

        //如果执行影响行数大于0，则修改成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }
    //向Administrator表中添加数据
    public int add(Warehouse productModel, Connection conn) throws SQLException {
        //预编译方式进行增加数据
        String s = "insert into warehouse (warehouseNo,name,address,volume,MaxVolume) value (?,?,?,?,?)";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,productModel.getWarehouseNo());
        preparedStatement.setString(2,productModel.getName());
        preparedStatement.setString(3,productModel.getAddress());
        preparedStatement.setDouble(4,productModel.getVolume());
        preparedStatement.setDouble(5,productModel.getMaxVolume());
        //如果执行影响行数大于0，则增加成功
        int id = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return id;
    }
    //在school表根据id删除相应数据
    public boolean delete(String warehouseNo, Connection conn) throws SQLException {
        //预编译方式进行删除数据
        String s = "delete from warehouse where  warehouseNo = ?";
        PreparedStatement preparedStatement = conn.prepareStatement(s);
        preparedStatement.setString(1,warehouseNo);
        //如果执行影响行数大于0，则增加成功
        int i = preparedStatement.executeUpdate();
        //关闭连接
        JdbcHelper.close(preparedStatement);
        return i > 0;
    }


}
